<?php
/**
 * 使用方法:php  +  此脚本  +  csv的ftp地址
 */
 error_reporting(0);
set_time_limit(0);
include_once dirname(dirname(dirname(dirname(dirname(__FILE__)))))."/common.php";
\ns_core\m_load::load("ns_model.message.message_explain");
\ns_core\m_load::load("ns_core.m_pinyin");
define('CP_ID','CCTV_GQ');
define('SP_ID','jscn');
class import_csv  extends  \ns_model\message\message_explain
{
    //ftp登录资源
    private $ftp_conn;

    //一次读入内存的条数
    private $read_memory_rows;

    //数据库实例
    private $db;

    //上游标识
    private $cp_id;

    //下游标识
    private $sp_id;

    //数据库字段映射关系
    private $arr_fileld = array(
        'nns_id'=>'ID',
        'nns_assetid'=>'ASSETID',
        'nns_category_id'=>'CATEGORY_ID',
        'nns_name'=>'节目名称',
        'nns_director'=>'导演',
        'nns_actor'=>'演员',
        'nns_poster'=>'海报',
        'nns_category_type'=>'CATEGORY_TYPE',
        'nns_index'=>'集数',
        'nns_area'=>'country_of_origin',
        'nns_year'=>'年代',
        'nns_type'=>'类型'
    );

    //实际数据库字段顺序
    private $asset_column_order = array();

    //ftp地址
    public $ftp_url;

    //下载csv到本地的路径
    private $path_local_csv ;

    //下载csv到本地的csv文件
    private $file_local_csv;

    public function __construct($ftp_url)
    {
         //获取sp  cp
        $arr_path = explode('|',str_replace(array('\\','/'),'|',__DIR__));
        $this->cp_id = CP_ID;
        $this->sp_id = SP_ID;
        $this->ftp_url  = $ftp_url;
        //csv下载到本地的目录
        $this->path_local_csv = "/tmp/stock_csv/cp_{$this->cp_id}/sp_{$this->sp_id}";
        //ftp校验，登录，下载
        $this->ftp_init();

        $this->db = m_config::get_dc()->db();

        //创建临时媒资表
        $this->create_table();
    }

    /**
     * description: 1.ftp地址校验    2.ftp登录   3.csv文件下载
     *
     */
    private function  ftp_init()
    {
        //验证ftp格式
        $ftp_url_info = parse_url($this->ftp_url);

        if( !is_array($ftp_url_info) || empty($ftp_url_info['path']) )
        {
            $this->returnMsg('ftp地址解析错误','die');
        }

        $arr_ftp_path_info = pathinfo($ftp_url_info['path']);

        if( empty($arr_ftp_path_info['extension']) || $arr_ftp_path_info['extension'] != 'csv' )
        {
            $this->returnMsg('不是csv的格式文件','die');
        }

        //ftp登录
        $this->my_ftp_login($ftp_url_info);

        //csv文件的相对路径
        $file_remote = trim($ftp_url_info['path'],'/');

        //判断ftp  csv文件大小
        $file_size = ftp_size($this->ftp_conn,$file_remote);

        if( empty($file_size) )
        {
            $this->returnMsg('csv文件内容为空','die');
        }
        if( !is_dir($this->path_local_csv) )
        {
            mkdir($this->path_local_csv,777,true);
        }

        //csv文件下载路径
        $this->file_local_csv = $this->path_local_csv.'/'.np_guid_rand().'.csv';
        //下载csv到本地
        $this->returnMsg('正在下载csv文件');
        $ftp_download_status = ftp_get($this->ftp_conn,$this->file_local_csv,$file_remote,FTP_BINARY);
	if( $ftp_download_status)
	{
	  $this->returnMsg('下载完成');
	}
	else
	{
		$this->returnMsg('下载失败','die');
	}
    }

    //外部调用接口
    public function run()
    {
        //csv入临时表
        $this->returnMsg('csv数据入临时表nns_cctv_gq开始');
        $this->import_temp_db();
        $this->returnMsg('csv数据入临时表nns_cctv_gq结束 开始去重');

        //去重
        $this->import_temp_group_db();
        $this->returnMsg('去重完毕');

        //入播控资源库
        $this->import_bk_db();
    }

    //csv数据入临时表
    private  function import_temp_db()
    {
        if( !is_file($this->file_local_csv))
        {
            $this->returnMsg('下载到本地的csv文件有误','die');
        }
        $file_res = fopen($this->file_local_csv,'r');

        while( !feof($file_res) )
        {
            $line_num = empty($line_num)?1:$line_num+1;

            $arr_line = fgetcsv($file_res);

            if( !is_array($arr_line) ||empty( $arr_line ) )
            {
                continue;
            }

            //把数据库所有数据都转换成utf8格式
            foreach( $arr_line as $index=>$line )
            {
                //转换字符编码
                $arr_line[$index] = iconv(mb_detect_encoding($line, array('GBK', 'UTF-8', 'UTF-16LE', 'UTF-16BE', 'ISO-8859-1')), 'UTF-8',$line );
            }

            if( $line_num == 1 && empty($this->asset_column_order) )
            {
                //首行字段处理
                $arr_column_org  = array_values($this->arr_fileld);

                foreach( $arr_line as $column )
                {
                    if( !in_array($column,$arr_column_org) )
                    {
                        $this->returnMsg("csv中{$column}字段非法",'die');
                    }
                    $this->asset_column_order[] = array_search($column,$this->arr_fileld);
                }

                $sql_asset_head = "insert into nns_cctv_gq(".implode(',',$this->asset_column_order).") values  ";
                $sql_asset_head_copy = $sql_asset_head;

                //创建数据库  asset_source_incre
                $this->create_table('asset');
            }
            else
            {
                //非首行字段的媒资数据

                $sql_new = '('.implode(',',array_map(array($this,'addslashes_and_quotes'),$arr_line)).'),';
                $sql_asset_head_copy.= $sql_new;
                //内存占用约为12%
                if( $line_num >= $this->read_memory_rows )
                {
                    nl_execute_by_db(trim($sql_asset_head_copy,','),$this->db);
                    $line_num = 1;
                    $sql_asset_head_copy = $sql_asset_head;
                }
            }
        }
        nl_execute_by_db(trim($sql_asset_head_copy,','),$this->db);
    }

    //group去重
    private function import_temp_group_db()
    {
        $sql = "truncate nns_cctv_gq_group";
        nl_execute_by_db($sql,$this->db);

        $sql = "insert into nns_cctv_gq_group
                            select * from nns_cctv_gq  group by nns_assetid";
        return nl_execute_by_db($sql,$this->db);
    }

    //入播控资源库
    private function import_bk_db()
    {
        //处理单集
        $this->returnMsg('单集入播控资源库开始');
        $this->init_simple_set();
        $this->returnMsg('单集结束开始多集入播控资源库');
        $this->init_multi_set();
        $this->returnMsg('多集入播控资源库开始');
    }

    //媒资名称处理
    private  function my_str_replace($str='')
    {
        $str = str_replace(' ', '', trim($str));
        if(strlen($str)<1)
        {
            return $str;
        }
        $str = str_replace('-', '', $str);
        if(strlen($str)<1)
        {
            return $str;
        }
        $str = str_replace(array(',','，','|','#','*'), '/', $str);
        $str = str_replace(' ', '', trim($str));
        return $str;
    }

    public function do_serice($arr_series)
    {
        $pinyin = m_pinyin::get_pinyin_letter($arr_series['nns_name']);
        $view_type = $arr_series['nns_index'] >1 ? '1' : '0';
        $add_series = array(
            'base_info' => array(
                'nns_name' => $arr_series['nns_name'],
                'nns_view_type' => $view_type,
                'nns_org_type' => '0',
                'nns_tag' => '26,',
                'nns_director' => $this->my_str_replace($arr_series['nns_director']),
                'nns_actor' => $this->my_str_replace($arr_series['nns_actor']),
                'nns_show_time' => (strlen($arr_series['nns_year']) > 0 && (int)$arr_series['nns_year'] > 1970 && (int)$arr_series['nns_year'] < 2020) ? (int)$arr_series['nns_year'] : date("Y"),
                'nns_view_len' => 0,
                'nns_all_index' => $arr_series['nns_index'],
                'nns_new_index' => $arr_series['nns_index']-1,
                'nns_area' => $this->my_str_replace($arr_series['nns_area']),
                'nns_image0' =>  '',
                'nns_image1' => '',
                'nns_image2' => '',
                'nns_image3' => '',
                'nns_image4' => '',
                'nns_image5' => '',
                'nns_summary' => '',
                'nns_remark' => '',
                'nns_category_id' => $arr_series['nns_category_id'],
                'nns_play_count' => '0',
                'nns_score_total' => '0',
                'nns_score_count' => '0',
                'nns_point' => '0',
                'nns_copyright_date' => (strlen($arr_series['nns_year']) > 0 && (int)$arr_series['nns_year'] > 1970 && (int)$arr_series['nns_year'] < 2020) ? (int)$arr_series['nns_year'] : date("Y"),
                'nns_asset_import_id' => $arr_series['nns_video_import_id'],
                'nns_pinyin' => $pinyin,
                'nns_pinyin_length' => strlen($pinyin),
                'nns_alias_name' => $arr_series['nns_name'],
                'nns_eng_name' => '',
                'nns_language' => '',
                'nns_text_lang' => '',
                'nns_producer' => '',
                'nns_screenwriter' => '',
                'nns_play_role' => '',
                'nns_copyright_range' => '',
                'nns_vod_part' => '',
                'nns_keyword' => '',
                'nns_import_source' => evn::get("project"),
                'nns_kind' => '',
                'nns_copyright' => '',
                'nns_clarity' => '',
                'nns_image_v' => '',
                'nns_image_s' => '',
                'nns_image_h' => '',
                'nns_cp_id' => $this->cp_id,
                'nns_conf_info' => '',
                'nns_ext_url' => '',
            ), //基本信息（存储于nns_vod表中）
            'ex_info' => array(
                'svc_item_id' => '',
                'month_clicks' => '',
                'week_clicks' => '',
                'base_id' => '',
                'asset_path' => '',
                'ex_tag' => '',
                'full_spell' => '',
                'awards' => '',
                'year' => '',
                'play_time' => '',
                'channel' => '',
                'first_spell' => '',
            ), //扩展信息（存储于nns_vod_ex表中）
        );
        //字段待修改4-14
        return $this->vod_action('add', $add_series);
    }

    public function do_index($arr_index)
    {
        $add_index = array(
            'base_info' => array(
                'nns_name' => $arr_index['nns_name'],
                'nns_index' => $arr_index['nns_index']-1,
                'nns_time_len' => '',
                'nns_summary' => '',
                'nns_image' => '',
                'nns_play_count' => 0,
                'nns_score_total' => 0,
                'nns_score_count' => 0,
                'nns_video_import_id' => $arr_index['nns_video_import_id'],
                'nns_import_id' => $arr_index['nns_index_import_id'],
                'nns_import_source' => evn::get("project"),

                'nns_director' => $this->my_str_replace($arr_index['nns_director']),
                'nns_actor' => $this->my_str_replace($arr_index['nns_actor']),
                'nns_release_time' => (strlen($arr_index['nns_year']) > 0 && (int)$arr_index['nns_year'] > 1970 && (int)$arr_index['nns_year'] < 2020) ? (int)$arr_index['nns_year'] : date("Y"),
                'nns_update_time' => date("Y-m-d"),
                'nns_watch_focus' => '',
                'nns_cp_id' => $arr_index['nns_cp_id'],
                'nns_conf_info' => '',
                'nns_ext_url' => '',
            ),
            'ex_info' => array(
                'isintact' => '',
                'subordinate_name' => '',
                'initials' => '',
                'publisher' => '',
                'first_spell' => '',
                'caption_language' => '',
                'language' => '',
                'region' => '',
                'adaptor' => '',
                'sreach_key' => '',
                'event_tag' => '',
                'year' => '',
                'sort_name' => '',
            ),
        );
        //分集注入
        return $this->index_action('add', $add_index);
    }

    public function do_media($arr_media)
    {
        $add_media = array(
            'base_info' => array(
                'nns_name' => $arr_media['nns_name'],
                'nns_type' => 1,
                'nns_url' => '',
                'nns_tag' => '26,',
                'nns_mode' => '',
                'nns_kbps' => '',
                'nns_content_id' => $arr_media['nns_assetid'],
                'nns_content_state' => 0,
                'nns_filetype' => 'ts',
                'nns_play_count' => '0',
                'nns_score_total' => '0',
                'nns_score_count' => '0',
                'nns_video_import_id' => $arr_media['nns_video_import_id'],
                'nns_index_import_id' => $arr_media['nns_index_import_id'],
                'nns_import_id' => $arr_media['nns_media_import_id'],
                'nns_import_source' => evn::get("project"),
                'nns_dimensions' => '2D',
                'nns_ext_url' => '',
                'nns_file_size' => 0,
                'nns_file_time_len' => 0,
                'nns_file_frame_rate' => 0,
                'nns_file_resolution' => 0,
                'nns_cp_id' => $arr_media['nns_cp_id'],
                'nns_ext_info' => '',
                'nns_drm_enabled' =>  0,
                'nns_drm_encrypt_solution' => '',
                'nns_drm_ext_info' => '',
                'nns_domain' => 0,
                'nns_media_type' => 1,
                'nns_original_live_id' => '',
                'nns_start_time' => '',
                'nns_media_service' => 'HTTP',
                'nns_conf_info' => '',
                'nns_encode_flag' => 0,
                'nns_live_to_media' => '',
                'nns_media_service_type' => '',
            ),
            'ex_info' => array(
                'file_hash' => '',
                'file_width' => '',
                'file_height' => '',
                'file_scale' => '',
                'file_coding' => '',
            ),
        );
        return  $this->media_action('add', $add_media);
    }

    //创建栏目
    private function do_category($category_name)
    {
        $do_category = array(
            'base_info'=>array(
                'nns_name'=>$category_name,   //栏目名称支持多层目录，多层目录的时候 /分割
                'nns_import_category_id'=>'', //栏目名称支持多层目录，多层目录的时候 /分割
                'nns_cp_id'=>$this->cp_id,
                'nns_import_parent_category_id'=>'',
                'nns_video_type'=>'0',  //媒资类型  0 点播 | 1 直播
            ),
        );
        $result_cate = $this->category_action('add', $do_category);

        if($result_cate['ret'] !=0 || !isset($result_cate['data_info']['base_info']['nns_id']) || strlen($result_cate['data_info']['base_info']['nns_id']) <1)
        {
            $this->returnMsg("创建栏目{$category_name}失败",'die');
        }
        return $result_cate;
    }

    //处理单集,单集的主媒资-分集-片源的注入id都为临时表的nns_assetid
    public function init_simple_set()
    {
        //创建单集栏目
        $result_cate = $this->do_category('存量单集');
        for( $circle_num = 0;true;$circle_num+=1000)
        {
            $sql="SELECT * FROM `nns_cctv_gq_group` 
                                where nns_state='0'  
                                and nns_index = 0
                                ORDER BY `nns_name`desc,
                                `nns_index` desc
                                LIMIT {$circle_num},1000";
            $result = nl_query_by_db($sql, $this->db);

            if( empty($result) || !is_array($result) )
            {
                break;
            }
            foreach ($result as $value)
            {
                //主媒资名字为空  nns_state 2
                if(strlen($value['nns_name']) <1)
                {
                    nl_execute_by_db("update nns_cctv_gq_group set nns_state='2' where nns_id='{$value['nns_id']}'", $this->db);
                    continue;
                }

                $value['nns_category_id'] = $result_cate['data_info']['base_info']['nns_id'];


                $value['nns_index'] = (int)$value['nns_index'] <1 ? 1 : (int)$value['nns_index'];
                $value['nns_cp_id']= $this->cp_id;

                $arr_series = $value;
                $arr_series['nns_video_import_id'] = $value['nns_assetid'];
                $this->do_serice($arr_series);
                $arr_index = $arr_series;
                $arr_index['nns_name'] = $arr_index['nns_name']."第{$value['nns_index']}集";
                $arr_index['nns_index_import_id'] = $value['nns_assetid'];
                $this->do_index($arr_index);
                $arr_media = $arr_index;
                $arr_media['nns_name'] = $arr_index['nns_name']."{$value['nns_id']}";
                $arr_media['nns_media_import_id'] = $value['nns_assetid'];
                $this->do_media($arr_media);
                //改变分组后的状态
                nl_execute_by_db("update nns_cctv_gq_group set nns_state='1' where nns_id='{$value['nns_id']}'",$this->db);

                //补充nns_asset_source_incre和nns_asset_source_incre_group表中主媒资id
                foreach( ['nns_cctv_gq_group','nns_cctv_gq'] as $table )
                {
                    $sql = "update {$table}  set nns_series_id = '{$value["nns_assetid"]}'  
                                                where nns_assetid = '{$value['nns_assetid']}'
                                                and (nns_series_id is null or length(nns_series_id) = 0)";
                    nl_execute_by_db($sql,$this->db);
                }
            }
        }
    }

    //多集,主媒资注入id为md5（主媒资名称）,分集-片源注入id为原媒资表中nns_assetid,实现一对多关联
    public function init_multi_set()
    {
        //创建单集栏目
        $result_cate = $this->do_category('存量多集');
        for( $circle_num = 0;true;$circle_num+=1000)
        {
            $sql="SELECT * FROM `nns_cctv_gq_group` 
                                where nns_state='0'  
                                and nns_index > 0
                                ORDER BY `nns_name`desc,
                                `nns_index` desc
                                LIMIT {$circle_num},1000";
            $result = nl_query_by_db($sql, $this->db);
            if( empty($result) || !is_array($result) )
            {
                break;
            }
            $last_data = null;

            foreach ($result as $value)
            {
                if(strlen($value['nns_name']) <1)
                {
                    nl_execute_by_db("update nns_cctv_gq_group set nns_state='2' where nns_id='{$value['nns_id']}'", m_config::get_dc()->db());
                    continue;
                }
                $str_name = str_replace(array("00{$value['nns_index']}","0{$value['nns_index']}","{$value['nns_index']}"),'',$value['nns_name']);

                $value['nns_cp_id'] = $this->cp_id;
                $video_id = md5($str_name);
                $value['nns_index'] = (int)$value['nns_index'] <1 ? 1 : (int)$value['nns_index'];
                if(!isset($last_data[$video_id]['video']))
                {
                    $video_value = $value;
                    $video_value['nns_name'] = $str_name;
                    $video_value['nns_index'] = (int)$value['nns_index'] <1 ? 1 : (int)$value['nns_index'];
                    $video_value['nns_video_import_id'] = $video_id;
                    $last_data[$video_id]['video'] = $video_value;
                }
                $index_value = $value;
                $index_value['nns_name'] = $str_name."第{$index_value['nns_index']}集";
                $index_value['nns_index_import_id'] = $value['nns_assetid'];
                $index_value['nns_video_import_id'] = $video_id;
                $last_data[$video_id]['index'][$value['nns_index']] = $index_value;
                $media_value = $index_value;
                $media_value['nns_name'] = $media_value['nns_name']."_{$media_value['nns_id']}";
                $media_value['nns_media_import_id'] = $value['nns_assetid'];
                $last_data[$video_id]['media'][] = $media_value;
                $last_data[$video_id]['ids'][] = $value['nns_id'];

                //补充nns_asset_source_incre和nns_asset_source_incre_group表中主媒资id,已存在媒资注入id的不再更新
                foreach( ['nns_cctv_gq_group','nns_cctv_gq'] as $table )
                {
                    $sql = "update {$table} set nns_series_id = '{$video_id}'  
                                                where nns_assetid = '{$value["nns_assetid"]}'
                                                and (nns_series_id is null or length(nns_series_id) = 0 )";
                    nl_execute_by_db($sql,$this->db);
                }

            }
            if(!is_array($last_data) || empty($last_data))
            {
                continue;
            }

            foreach ($last_data as $in_key=>$in_value)
            {
                $in_value['video']['nns_category_id'] = $result_cate['data_info']['base_info']['nns_id'];
                $this->do_serice($in_value['video']);
                foreach ($in_value['index'] as $in_index_value)
                {
                    $this->do_index($in_index_value);
                }
                foreach ($in_value['media'] as $in_media_value)
                {
                    $this->do_media($in_media_value);
                }
                //注入资源库成功，状态置为1
                nl_execute_by_db("update nns_cctv_gq_group set nns_state='1' where nns_id in('".implode("','", $in_value['ids'])."')", $this->db);
            }
        }
    }

    //ftp登录
    private function my_ftp_login($ftp_url_info)
    {
        if( !isset($ftp_url_info['user']) || !isset($ftp_url_info['pass']) || !isset($ftp_url_info['path']) || !isset($ftp_url_info['host']))
        {
            $this->returnMsg('ftp链接不正确');
        }

        $this->ftp_conn = ftp_connect($ftp_url_info['host']);
        if( !$this->ftp_conn )
        {
            $this->returnMsg('无法连接ftp服务');
        }

        $ftp_login_status = ftp_login($this->ftp_conn,$ftp_url_info['user'],$ftp_url_info['pass']);
        if( !$ftp_login_status )
        {
            $this->returnMsg('登录信息不正确');
        }
    }

    //创建数据表
    private function create_table()
    {
        //栏目sql
        $arr_table =['nns_cctv_gq','nns_cctv_gq_group'];
        foreach( $arr_table as $table )
        {
            $sql = "CREATE TABLE `{$table}` (
                      `nns_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'GUID',
                      `nns_assetid` varchar(255) NOT NULL DEFAULT '' COMMENT '媒资id',
                      `nns_category_id` varchar(255) NOT NULL DEFAULT '' COMMENT '栏目id',
                      `nns_director` varchar(255) NOT NULL DEFAULT '' COMMENT '导演',
                      `nns_name` varchar(255) NOT NULL DEFAULT '' COMMENT '媒资名称',
                      `nns_actor` varchar(255) NOT NULL DEFAULT '' COMMENT '演员',
                      `nns_poster` varchar(255) NOT NULL DEFAULT '' COMMENT '海报',
                      `nns_category_type` varchar(255) NOT NULL DEFAULT '' COMMENT '栏目类型',
                      `nns_index` tinyint(10) NOT NULL COMMENT '分集数',
                      `nns_area` varchar(255) NOT NULL DEFAULT '' COMMENT '地区',
                      `nns_year` varchar(255) NOT NULL DEFAULT '' COMMENT '年份',
                      `nns_type` varchar(255) NOT NULL DEFAULT '' COMMENT '类型',
                      `nns_state` tinyint(2)  NOT NULL DEFAULT 0 COMMENT '0-初始状态  1-注入资源库成功  2-注入资源库失败',
                      `nns_series_id` VARCHAR(255)  DEFAULT NULL COMMENT '主媒资注入id',
                      KEY `nns_assetid` (`nns_assetid`) USING BTREE,
                      KEY `nns_id` (`nns_id`) USING BTREE
                    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

            nl_execute_by_db('use nn_bk',$this->db);
            //如果存在则删除
            nl_execute_by_db("DROP TABLE IF EXISTS `{$table}`;",$this->db);
            $re_create_table = nl_execute_by_db($sql,$this->db);

            if( empty($re_create_table) )
            {
                $this->returnMsg("数据库{$table}创建失败");
            }
        }
    }

    //拼接sql时先转义后，后添加单引号
    private function addslashes_and_quotes($str) {
        return sprintf("'%s'", addslashes($str));
    }

    /**
     * @param $msg     脚本信息
     * @param null $type    信息类型   die 终止信息  |  其他  一般提示信息
     * 脚本执行信息
     */
    public function returnMsg($msg,$type = null)
    {
        if( is_string($msg) && !empty($msg))
        {
            echo "-------{$msg}-------".PHP_EOL;
            if($type == 'die')
            {
                echo "-------进程结束-------".PHP_EOL;
                exit;
            }
        }
    }
}

//改成图形界面
if( empty($argv[1]) )
{
    exit("csv的ftp路径不能为空".PHP_EOL);
}
else
{
    //首次进入这个
    if( strpos($argv[1],'ftp://') !== 0 )
    {
        exit('----------需是ftp连接-----------');
    }

    $obj = new import_csv($argv[1]);

    $obj->run();
}







